﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ZX.EHR.Library;
using ZX.BLL.Extend;
using ZX.SYS.Library;
using ZX.CRM.Library;
using ZX.Common;
using ZX.DAL.DBExtend;
using System.Data;
using System.Data.SqlClient;
namespace ZX.CRM.Bussiness
{
    public class BTCrmCustomer : BaseBussnies
    {
        /// <summary>
        /// 根据ID获取客户数据
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public IList<CrmCustomer> GetCrmCustomer(Guid ID)
        {
            this.entity = new CrmCustomer();
            return GetEntitysBySqlComm<CrmCustomer>("select * from CrmCustomer where ID='" + ID + "'");
        }
        /// <summary>
        /// 根据ID获取客户信息数据
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public IList<CrmCustomerInfo> GetCrmCustomerInfo(Guid ID)
        {
            this.entity = new CrmCustomerInfo();
            return GetEntitysBySqlComm<CrmCustomerInfo>("select * from CrmCustomerInfo where ID='" + ID + "'");
        }
        /// <summary>
        /// 获取客户区域
        /// </summary>
        /// <returns></returns>
        public IList<DatDepartment> GetVirtualDepartment(Guid? userId)
        {
            this.entity = new DatDepartment();
            return GetEntitysBySqlComm<DatDepartment>("select distinct DeptName,DeptID from DatDepartment where IsVirtualDepartment=1 and DeptID in (select value from fn_Split((select  DEPTIDS from V_UserEmp where userid='" + userId + "'),',')) order by DeptName");
        }
        /// <summary>
        /// 获取客户区域(新)
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public IList<V_CrmUserArea> GetVirtualDepartmentNew(Guid? userId)
        {
            this.entity = new V_CrmUserArea();
            return GetEntitysBySqlComm<V_CrmUserArea>("select * from V_CrmUserArea where UserID='" + userId + "' order by DeptName");
        }

        /// <summary>
        /// 获取所有联系人信息
        /// </summary>
        /// <returns></returns>
        public IList<V_CrmLinkMan> GetAllLinkMan()
        {
            this.entity = new V_CrmLinkMan();
            return GetEntitysBySqlComm<V_CrmLinkMan>("select * from V_CrmLinkMan order by Caption");
        }

        /// <summary>
        /// 获取所有产品信息
        /// </summary>
        /// <returns></returns>
        public IList<CrmProduct> GetAllCrmProduct()
        {
            this.entity = new CrmProduct();
            return GetEntitysBySqlComm<CrmProduct>("select * from CrmProduct order by Caption");
        }
        /// <summary>
        /// 获取所有产品包信息
        /// </summary>
        /// <returns></returns>
        public IList<CrmProductPack> GetAllCrmProductPack()
        {
            this.entity = new CrmProductPack();
            return GetEntitysBySqlComm<CrmProductPack>("select * from CrmProductPack order by packname");
        }
        /// <summary>
        /// 获取所有现场销售信息
        /// </summary>
        /// <returns></returns>
        public V_CrmLiveOrDerManager GetLiveSaleInfo(Guid id)
        {
            this.entity = new V_CrmLiveOrDerManager();
            return GetEntityBySqlComm("select * from V_CrmLiveOrDerManager where Id='" + id + "' order by CustomerName") as V_CrmLiveOrDerManager;
        }
        /// <summary>
        /// 获取所有联系人信息
        /// </summary>
        /// <returns></returns>
        public IList<V_CrmTempPay> GetAllListPay(Guid payid)
        {
            this.entity = new V_CrmTempPay();
            return GetEntitysBySqlComm<V_CrmTempPay>("select * from V_CrmTempPay where PayId='" + payid + "' order by Caption");
        }
        /// <summary>
        /// 获取所有联系人信息
        /// </summary>
        /// <returns></returns>
        public IList<V_CrmLinkMan> GetLinkManById(string id)
        {
            this.entity = new V_CrmLinkMan();
            return GetEntitysBySqlComm<V_CrmLinkMan>("select * from V_CrmLinkMan where CustomID='" + CSVHelper.FmSQL(id) + "' order by Caption");
        }
        /// <summary>
        /// 编辑客户信息时获取所有信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public IList<V_CrmCustomer> GetAllCustomerInfo(Guid Id)
        {
            this.entity = new V_CrmCustomer();
            return GetEntitysBySqlComm<V_CrmCustomer>("select * from V_CrmCustomer where ID='" + Id + "'");
        }
        /// <summary>
        /// 编辑客户信息时获取所有信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public IList<V_CrmCustomerInfo> GetAllV_CrmCustomerInfos(Guid Id)
        {
            this.entity = new V_CrmCustomerInfo();
            return GetEntitysBySqlComm<V_CrmCustomerInfo>("select * from V_CrmCustomerInfo where ID='" + Id + "'");
        }
        /// <summary>
        /// 编辑渠道客户信息时获取所有信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public IList<V_CrmCustomerChannel> GetAllV_CrmCustomerChannel(Guid WorkFlowInsId)
        {
            this.entity = new V_CrmCustomerChannel();
            return GetEntitysBySqlComm<V_CrmCustomerChannel>("select * from V_CrmCustomerChannel where WorkFlowInsId='" + WorkFlowInsId + "'");
        }
        /// <summary>
        /// 编辑客户信息时获取信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public V_CrmActivity GetCustomerCallOnInfo(Guid Id)
        {
            this.entity = new V_CrmActivity();
            return GetEntityBySqlComm("select * from V_CrmActivity where ID='" + Id + "'") as V_CrmActivity;
        }
        /// <summary>
        /// 编辑客户信息时获取信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public V_CrmActivityCall GetCallOnInfo(Guid Id)
        {
            this.entity = new V_CrmActivityCall();
            return GetEntityBySqlComm("select * from V_CrmActivityCall where ID='" + Id + "'") as V_CrmActivityCall;
        }
        /// <summary>
        /// 编辑客户信息时获取信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public V_CrmActivityTel GetTelInfo(Guid Id)
        {
            this.entity = new V_CrmActivityTel();
            return GetEntityBySqlComm("select * from V_CrmActivityTel where ID='" + Id + "'") as V_CrmActivityTel;
        }
        /// <summary>
        /// 编辑客户信息时获取所有信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public IList<V_CrmActivity> GetAllCallInfo(Guid Id)
        {
            this.entity = new V_CrmActivity();
            return GetEntitysBySqlComm<V_CrmActivity>("select * from V_CrmActivity where CustomId='" + Id + "'");
        }
        /// <summary>
        /// 编辑客户分配信息时获取所有信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public IList<V_CrmCustomerInfo> GetAllCustomerIn(Guid Id)
        {
            this.entity = new V_CrmCustomerInfo();
            return GetEntitysBySqlComm<V_CrmCustomerInfo>("select * from V_CrmCustomerInfo where ID='" + Id + "'");
        }
        /// <summary>
        /// 编辑客户信息时获取所有信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public V_CrmCustomer GetCustomerInfo(Guid Id)
        {
            this.entity = new V_CrmCustomer();
            return GetEntityBySqlComm("select * from V_CrmCustomer where ID='" + Id + "'") as V_CrmCustomer;
        }
        /// <summary>
        /// 获取客户信息时获取所有信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public V_CrmCustomerInfo GetV_CrmCustomerInfo(Guid Id)
        {
            this.entity = new V_CrmCustomerInfo();
            return GetEntityBySqlComm("select * from V_CrmCustomerInfo where ID='" + Id + "'") as V_CrmCustomerInfo;
        }
        /// <summary>
        /// 获取部门所有信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public DatDepartment GetDatDepartInfo(Guid Id)
        {
            this.entity = new DatDepartment();
            return GetEntityBySqlComm("select * from DatDepartment where deptID='" + Id + "'") as DatDepartment;
        }
        /// <summary>
        /// 获取私海列表
        /// </summary>
        /// <returns></returns>
        public IList<V_CrmPrivateCustom> GetAllCustomerByEmp(int num, Guid gd)
        {
            this.entity = new V_CrmPrivateCustom();
            return GetEntitysBySqlComm<V_CrmPrivateCustom>("select top " + CSVHelper.FmSQL(num) + " * from V_CrmPrivateCustom where CustomUser='" + CSVHelper.FmSQL(gd) + "'  order by AddTime desc");
        }
        /// <summary>
        /// 共享时获取所有信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public V_CrmPrivateCustom GetCrmShareInfo(Guid Id)
        {
            this.entity = new V_CrmPrivateCustom();
            return this.GetEntityBySqlComm("select * from V_CrmPrivateCustom where CustomId='" + CSVHelper.FmSQL(Id) + "'") as V_CrmPrivateCustom;
        }
        /// <summary>
        /// 根据客户ID判断私海表中私海存在该记录
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public bool GetCrmPrivateCustomer(Guid customerId)
        {
            this.entity = new CrmPrivateCustom();
            DbSql db = new DbSql();
            string strSql = "select count(*) from CrmPrivateCustom where CustomId='" + customerId + "'";
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 编辑联系人时获取所有信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public CrmLinkMan GetCrmLinkManInfo(Guid Id)
        {
            this.entity = new CrmLinkMan();
            return this.GetEntityBySqlComm("select * from CrmLinkMan where ID='" + CSVHelper.FmSQL(Id) + "'") as CrmLinkMan;
        }
        /// <summary>
        /// 编辑缴款单时获取所有信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public CrmPay GetCrmPayInfo(Guid Id)
        {
            this.entity = new CrmPay();
            return this.GetEntityBySqlComm("select * from CrmPay where ID='" + CSVHelper.FmSQL(Id) + "'") as CrmPay;
        }
        /// <summary>
        /// 编辑缴款单时获取所有信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public V_CrmProductPayValue GetV_CrmPayInfo(Guid Id)
        {
            this.entity = new V_CrmProductPayValue();
            return this.GetEntityBySqlComm("select * from V_CrmProductPayValue where ID='" + CSVHelper.FmSQL(Id) + "'") as V_CrmProductPayValue;
        }
        /// <summary>
        /// 活动管理获取所有信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public V_CrmActivity GetCrmActivity(Guid Id)
        {
            this.entity = new V_CrmActivity();
            return this.GetEntityBySqlComm("select * from V_CrmActivity where ID='" + CSVHelper.FmSQL(Id) + "'") as V_CrmActivity;
        }
        /// <summary>
        /// 根据客户ID获取联系人信息
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<CrmActivity> GetCrmActivityByCodeTypeId(string code, Guid typeId)
        {
            this.entity = new CrmActivity();
            return this.GetEntitys<CrmActivity>("code='" + CSVHelper.FmSQL(code) + "' and typeId='" + CSVHelper.FmSQL(typeId) + "' order by PlanEndDate desc");
        }
        /// <summary>
        /// 根据客户ID获取联系人信息
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<V_CrmLinkMan> GetLinkManInfoByCustomerId(Guid CustomerId)
        {
            this.entity = new V_CrmLinkMan();
            return GetEntitysBySqlComm<V_CrmLinkMan>("select * from V_CrmLinkMan where CustomId='" + CustomerId + "' order by AddTime desc");
        }
        /// <summary>
        /// 根据客户ID获取接洽人信息
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<V_CrmLinkMan> GetJieQiaRenInfoByCustomerId(Guid CustomerId)
        {
            this.entity = new V_CrmLinkMan();
            return GetEntitysBySqlComm<V_CrmLinkMan>("select * from V_CrmLinkMan where CustomId='" + CustomerId + "' and TypeID='ADFA4C2D-42C4-411F-B47E-9AA9F069A119' order by AddTime desc");
        }
        /// <summary>
        /// 根据客户ID获取联系人信息
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<V_CrmManager> GetLinkManInfosByCustomerId(Guid CustomerId)
        {
            this.entity = new V_CrmManager();
            return GetEntitysBySqlComm<V_CrmManager>("select * from V_CrmManager where CustomId='" + CustomerId + "' order by AddTime desc");
        }
        /// <summary>
        /// 根据客户ID获取联系人信息
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<V_CrmLinkMan> GetManageByCustomerId(Guid CustomerId)
        {
            this.entity = new V_CrmLinkMan();
            return GetEntitysBySqlComm<V_CrmLinkMan>("select * from V_CrmLinkMan where CustomId='" + CustomerId + "' order by AddTime desc");
        }
        /// <summary>
        /// 根据客户ID获取联系人信息
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<V_CrmManager> GetLinkManagertInfoByCustomerId(Guid CustomerId)
        {
            this.entity = new V_CrmManager();
            return GetEntitysBySqlComm<V_CrmManager>("select * from V_CrmManager where CustomId='" + CustomerId + "' order by AddTime desc");
        }
        /// <summary>
        /// 根据客户ID获取联系记录信息
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<V_CrmActivityTel> GetLinkRecordInfoByCustomerId(Guid CustomerId)
        {
            this.entity = new V_CrmActivityTel();
            return GetEntitysBySqlComm<V_CrmActivityTel>("select * from V_CrmActivityTel where CustomId='" + CustomerId + "' and (AnticipeResult is null or AnticipeResult='') order by AddTime desc");
        }
        /// <summary>
        /// 根据客户ID获取商机任务记录信息
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<V_CrmActivityCall> GetLinkChoiceInfoByCustomerId(Guid CustomerId)
        {
            this.entity = new V_CrmActivityCall();
            return GetEntitysBySqlComm<V_CrmActivityCall>("select * from V_CrmActivityCall where CustomId='" + CustomerId + "' and (AnticipeResult is not null or AnticipeResult<>'') order by AddTime desc");
        }
        /// <summary>
        /// 根据客户ID获取联系记录信息
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<V_CrmActivity> GetLinkRecordInfoById(Guid CustomerId, string str)
        {
            this.entity = new V_CrmActivity();
            return GetEntitysBySqlComm<V_CrmActivity>("select * from V_CrmActivity where CustomId='" + CustomerId + "' and PhraseText='" + str + "'");
        }
        /// <summary>
        /// 转为意向客户是否存在
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <param name="AddUser">用户ID</param>
        /// <returns></returns>
        public bool IsExistWillCustomer(Guid CustomerId, Guid AddUser)
        {
            this.entity = new CrmWillCustomer();
            DbSql db = new DbSql();
            string strSql = "select count(*) from CrmWillCustomer where CustomerID='" + CustomerId + "' and AddUser='" + AddUser + "'";
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 转为准备客户是否存在
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <param name="AddUser">用户ID</param>
        /// <returns></returns>
        public bool IsExistReadyCustomer(Guid CustomerId, Guid AddUser)
        {
            this.entity = new CrmCustomerReady();
            DbSql db = new DbSql();
            string strSql = "select count(*) from CrmCustomerReady where CustomerID='" + CustomerId + "' and AddUser='" + AddUser + "'";
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 转为私海是否已存在
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public bool IsExistPrivateCustomer(Guid CustomerId)
        {
            this.entity = new CrmCustomer();
            DbSql db = new DbSql();
            string strSql = "select count(*) from CrmCustomer where ID='" + CustomerId + "' and IsPrevate=1";
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取私有客户最大数
        /// </summary>
        /// <returns></returns>
        public int GetPrivateNum(string strType)
        {
            try
            {
                int iAllCount = 0;
                DbSql db = new DbSql();
                iAllCount = Convert.ToInt32(db.GetSingle("select Contents from CrmSetinfo where Code='" + strType + "'").ToString());
                return iAllCount;
            }
            catch (Exception)
            {
                return 0;
                throw;
            }
            
        }
        /// <summary>
        /// 单独设置的私有客户最大数
        /// </summary>
        /// <returns></returns>
        public int GetPrivateNumBySet(string strUserID)
        {
            try
            {
                int iAllCount = 0;
                DbSql db = new DbSql();
                iAllCount = Convert.ToInt32(db.GetSingle("select PrivateNum from CrmPrivateNumSet where UserID='" + strUserID + "'").ToString());
                return iAllCount;
            }
            catch (Exception)
            {
                return 0;
                throw;
            }
            
        }
        /// <summary>
        /// 根据所有部门信息
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<DatDepartment> GetDatDepartment()
        {
            this.entity = new DatDepartment();
            return GetEntitysBySqlComm<DatDepartment>("select * from DatDepartment where IsVirtualDepartment<>1 or IsVirtualDepartment is null order by deptpath");
        }

        #region==================== 判断 ====================
        /// <summary>
        /// 判断客户名是否已存在(CrmCustomer)
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <param name="strType">1：新增 2：修改 3:录入客户新增 4:修改客户</param>
        /// <param name="strName">客户名</param>
        /// <returns></returns>
        public bool CrmCustomerNameIsExists(Guid CustomerId, string strType, string strName)
        {
            DbSql db = new DbSql();
            string strSql = "";
            //新增
            if (strType == "1" || strType == "3")
            {
                strSql = string.Format("select count(*) from CrmCustomer where CustomerName='" + strName + "'");
            }
            //修改
            else if (strType == "2" || strType == "4")
            {
                strSql = string.Format("select count(*) from CrmCustomer where ID<>'" + CustomerId + "' and CustomerName='" + strName + "'");
            }
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 判断客户名是否已存在(CrmCustomerInfo)
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <param name="strType">1：新增 2：修改 3:录入客户新增 4:修改客户</param>
        /// <param name="strName">客户名</param>
        /// <returns></returns>
        public bool CrmCustomerInfoNameIsExists(Guid CustomerId, string strType, string strName)
        {
            DbSql db = new DbSql();
            string strSql = "";
            //新增
            if (strType == "1" || strType == "3")
            {
                strSql = string.Format("select count(*) from CrmCustomerInfo where CustomerName='" + strName + "'");
            }
            //修改
            else if (strType == "2" || strType == "4")
            {
                strSql = string.Format("select count(*) from CrmCustomerInfo where ID<>'" + CustomerId + "' and CustomerName='" + strName + "'");
            }
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 判断客户名是否已存在(CrmCustomer) like
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <param name="strType">1：新增 2：修改 3:录入客户新增 4:修改客户</param>
        /// <param name="strName">客户名</param>
        /// <returns></returns>
        public bool CrmCustomerNameIsExistsLikeName(Guid CustomerId, string strType, string strName)
        {
            DbSql db = new DbSql();
            string strSql = "";
            //新增
            if (strType == "1" || strType == "3")
            {
                strSql = string.Format("select count(*) from CrmCustomer where CustomerName like '%" + strName + "%'");
            }
            //修改
            else if (strType == "2" || strType == "4")
            {
                strSql = string.Format("select count(*) from CrmCustomer where ID<>'" + CustomerId + "' and CustomerName like '%" + strName + "%'");
            }
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 判断客户名是否已存在(CrmCustomerInfo) like
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <param name="strType">1：新增 2：修改 3:录入客户新增 4:修改客户</param>
        /// <param name="strName">客户名</param>
        /// <returns></returns>
        public bool CrmCustomerInfoNameIsExistsLikeName(Guid CustomerId, string strType, string strName)
        {
            DbSql db = new DbSql();
            string strSql = "";
            //新增
            if (strType == "1" || strType == "3")
            {
                strSql = string.Format("select count(*) from CrmCustomerInfo where CustomerName like '%" + strName + "%'");
            }
            //修改
            else if (strType == "2" || strType == "4")
            {
                strSql = string.Format("select count(*) from CrmCustomerInfo where ID<>'" + CustomerId + "' and CustomerName like '%" + strName + "%'");
            }
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 判断决策人手机是否已存在(CrmCustomer)
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <param name="strType">1：新增 2：修改 3:录入客户新增 4:修改客户</param>
        /// <param name="strName">客户名</param>
        /// <returns>0:正常  1:手机1存在  2:手机2存在</returns>
        public int CrmCustomerTelIsExists(Guid CustomerId, string strType, string strName)
        {
            DbSql db = new DbSql();
            string strSql1 = "", strSql2 = "";
            //新增
            if (strType == "1" || strType == "3")
            {
                strSql1 = string.Format("select count(*) from CrmCustomer where DecisionTel='" + strName + "'");
                strSql2 = string.Format("select count(*) from CrmCustomer where DecisionTel2='" + strName + "'");
            }
            //修改
            else if (strType == "2" || strType == "4")
            {
                strSql1 = string.Format("select count(*) from CrmCustomer where ID<>'" + CustomerId + "' and DecisionTel='" + strName + "'");
                strSql2 = string.Format("select count(*) from CrmCustomer where ID<>'" + CustomerId + "' and DecisionTel2='" + strName + "'");
            }
            int iCount1 = db.ExecuteScalar(strSql1);
            if (iCount1 > 0)
            {
                return 1;
            }
            else
            {
                if (strName != "")
                {
                    int iCount2 = db.ExecuteScalar(strSql2);
                    if (iCount2 > 0)
                    {
                        return 2;
                    }
                    else
                    {
                        return 0;
                    }
                }
                else
                {
                    return 0;
                }
            }
        }
        /// <summary>
        /// 判断决策人手机是否已存在(CrmCustomerInfo)
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <param name="strType">1：新增 2：修改 3:录入客户新增 4:修改客户</param>
        /// <param name="strName">客户名</param>
        /// <returns>0:正常  1:手机1存在  2:手机2存在</returns>
        public int CrmCustomerInfoTelIsExists(Guid CustomerId, string strType, string strName)
        {
            DbSql db = new DbSql();
            string strSql1 = "", strSql2 = "";
            //新增
            if (strType == "1" || strType == "3")
            {
                strSql1 = string.Format("select count(*) from CrmCustomerInfo where DecisionTel='" + strName + "'");
                strSql2 = string.Format("select count(*) from CrmCustomerInfo where DecisionTel2='" + strName + "'");
            }
            //修改
            else if (strType == "2" || strType == "4")
            {
                strSql1 = string.Format("select count(*) from CrmCustomerInfo where ID<>'" + CustomerId + "' and DecisionTel='" + strName + "'");
                strSql2 = string.Format("select count(*) from CrmCustomerInfo where ID<>'" + CustomerId + "' and DecisionTel2='" + strName + "'");
            }
            int iCount1 = db.ExecuteScalar(strSql1);
            if (iCount1 > 0)
            {
                return 1;
            }
            else
            {
                if (strName != "")
                {
                    int iCount2 = db.ExecuteScalar(strSql2);
                    if (iCount2 > 0)
                    {
                        return 2;
                    }
                    else
                    {
                        return 0;
                    }
                }
                else
                {
                    return 0;
                }
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <param name="strType"></param>
        /// <param name="strName"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public int SaveCrmCustomerTelIsExists(Guid CustomerId, string strType, string strName)
        {
            int iResult = 0;
            DbSql db = new DbSql();
            string strSql1 = "", strSql2 = "", strSql3 = "", strSql4 = "";
            //新增
            if (strType == "1" || strType == "3")
            {
                strSql1 = string.Format("select count(*) from CrmCustomer where DecisionTel='" + strName + "'");
                strSql2 = string.Format("select count(*) from CrmCustomer where DecisionTel2='" + strName + "'");
                strSql3 = string.Format("select count(*) from CrmCustomerInfo where DecisionTel='" + strName + "'");
                strSql4 = string.Format("select count(*) from CrmCustomerInfo where DecisionTel2='" + strName + "'");
            }
            //修改
            else if (strType == "2" || strType == "4")
            {
                strSql1 = string.Format("select count(*) from CrmCustomer where ID<>'" + CustomerId + "' and DecisionTel='" + strName + "'");
                strSql2 = string.Format("select count(*) from CrmCustomer where ID<>'" + CustomerId + "' and DecisionTel2='" + strName + "'");
                strSql3 = string.Format("select count(*) from CrmCustomerInfo where ID<>'" + CustomerId + "' and DecisionTel='" + strName + "'");
                strSql4 = string.Format("select count(*) from CrmCustomerInfo where ID<>'" + CustomerId + "' and DecisionTel2='" + strName + "'");
            }
            int iCount1 = db.ExecuteScalar(strSql1);
            if (iCount1 > 0)
            {
                iResult = 1;  //手机1 CrmCustomer
            }
            else
            {
                int iCount2 = db.ExecuteScalar(strSql2);
                if (iCount2 > 0)
                {
                    iResult = 2;  //手机2 CrmCustomer
                }
                else
                {
                    int iCount3 = db.ExecuteScalar(strSql3);
                    if (iCount3 > 0)
                    {
                        iResult = 3;  //手机1 CrmCustomerInfo
                    }
                    else
                    {
                        int iCount4 = db.ExecuteScalar(strSql4);
                        if (iCount4 > 0)
                        {
                            iResult = 4;  //手机2 CrmCustomerInfo
                        }
                    }
                }
            }
            return iResult;
        }
        /// <summary>
        /// 判断LinkMan 手机是否已存在
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <param name="strType">1：新增 2：修改 3:录入客户新增 4:修改客户</param>
        /// <param name="strName">客户名</param>
        /// <returns></returns>
        public bool CrmLinkManTelIsExists(string Id, string strType, string strTel)
        {
            DbSql db = new DbSql();
            string strSql = "";
            //新增
            if (strType == "insert")
            {
                strSql = string.Format("select count(*) from CrmLinkMan where MobileTel='" + strTel + "'");
            }
            //修改
            else if (strType == "update")
            {
                strSql = string.Format("select count(*) from CrmLinkMan where ID<>'" + Id + "' and MobileTel='" + strTel + "'");
            }
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 判断LinkMan name and customerid是否已存在
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <param name="strType">1：新增 2：修改 3:录入客户新增 4:修改客户</param>
        /// <param name="strName">客户名</param>
        /// <returns></returns>
        public bool CrmLinkManNameIsExists(string Id, string strType, string cid, string strName)
        {
            DbSql db = new DbSql();
            string strSql = "";
            //新增
            if (strType == "insert")
            {
                strSql = string.Format("select count(*) from CrmLinkMan where CustomId='" + cid + "' and Caption='" + strName + "'");
            }
            //修改
            else if (strType == "update")
            {
                strSql = string.Format("select count(*) from CrmLinkMan where ID<>'" + Id + "' and CustomId='" + cid + "' and Caption='" + strName + "'");
            }
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        /// <summary>
        /// 获取所属附件
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<V_CrmCustomerAttach> GetAllCrmCustomerAttachInfo(Guid CustomerId)
        {
            this.entity = new V_CrmCustomerAttach();
            return GetEntitysBySqlComm<V_CrmCustomerAttach>("select * from V_CrmCustomerAttach where ID in(select value from fn_Split((select AttachList from CrmCustomer where ID='" + CustomerId + "'),','))  order by AddTime desc");
        }
        /// <summary>
        /// 获取所属附件
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<V_CrmCustomerAttach> GetAllCrmCustomerInfoAttachInfo(Guid CustomerId)
        {
            this.entity = new V_CrmCustomerAttach();
            return GetEntitysBySqlComm<V_CrmCustomerAttach>("select * from V_CrmCustomerAttach where ID in(select value from fn_Split((select AttachList from CrmCustomerInfo where ID='" + CustomerId + "'),','))  order by AddTime desc");
        }
        /// <summary>
        /// 查找所有的省份
        /// </summary>
        /// <returns></returns>
        public IList<SysProvince> GetAllProvince()
        {
            this.entity = new SysProvince();
            return GetEntitysBySqlComm<SysProvince>("select * from SysProvince order by Code");
        }
        /// <summary>
        /// 根据省份获取城市
        /// </summary>
        /// <param name="proviceId"></param>
        /// <returns></returns>
        public IList<SysCity> GetAllCityByProviceName(string ProviceName)
        {
            this.entity = new SysCity();
            return GetEntitysBySqlComm<SysCity>("select * from SysCity where ProvinceId=(select Code from SysProvince where Name='" + ProviceName + "') order by Code");
        }
        /// <summary>
        /// 根据城市获取区县
        /// </summary>
        /// <param name="CityName"></param>
        /// <returns></returns>
        public IList<SysArea> GetAllAreaByCityName(string CityName)
        {
            this.entity = new SysArea();
            return GetEntitysBySqlComm<SysArea>("select * from SysArea where CityId=(select Code from SysCity where Name='" + CityName + "')");
        }
        /// <summary>
        /// 获取省份信息
        /// </summary>
        /// <param name="strProvince">省份</param>
        /// <returns></returns>
        public IList<V_SysProvince> GetProvinceInfo(string strProvince)
        {
            this.entity = new V_SysProvince();
            return GetEntitysBySqlComm<V_SysProvince>("select * from V_SysProvince where Province='" + strProvince + "'");
        }
        /// <summary>
        /// 获取省份信息
        /// </summary>
        /// <param name="strProvince">省份</param>
        /// <param name="strCity">城市</param>
        /// <returns></returns>
        public IList<V_SysProvince> GetProvinceInfo(string strProvince, string strCity)
        {
            this.entity = new V_SysProvince();
            return GetEntitysBySqlComm<V_SysProvince>("select * from V_SysProvince where Province='" + strProvince + "' and City='" + strCity + "'");
        }
        /// <summary>
        /// 获取省份信息
        /// </summary>
        /// <param name="strProvince">省份</param>
        /// <param name="strCity">城市</param>
        /// <param name="strArea">区县</param>
        /// <returns></returns>
        public IList<V_SysProvince> GetProvinceInfo(string strProvince, string strCity, string strArea)
        {
            this.entity = new V_SysProvince();
            return GetEntitysBySqlComm<V_SysProvince>("select * from V_SysProvince where Province='" + strProvince + "' and City='" + strCity + "' and Area='" + strArea + "'");
        }
        /// <summary>
        /// 添加客户时判断是否还能添加客户
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="startTime">当天开始时间</param>
        /// <param name="endTime">当天结束时间</param>
        /// <returns></returns>
        public int GetCrmCustomerCount(Guid userId, string startTime, string endTime)
        {
            int iCustomerCount = 0;
            DbSql db = new DbSql();
            iCustomerCount = Convert.ToInt32(db.GetSingle("select count(*) as counts from CrmCustomer where AddUser='" + userId + "' and AddTime between '" + startTime + "' and '" + endTime + "'").ToString());
            return iCustomerCount;
        }
        public string UpdateCrmPay(string strProcedure, IDataParameter[] sp)
        {
            string iCustomerCount = "";
            int iResult = 0;
            DbSql db = new DbSql();
            iCustomerCount = db.RunProcedureReturnString(strProcedure, sp, out iResult);
            return iCustomerCount;
        }

        /// <summary>
        /// 客户状态为公海
        /// </summary>
        /// <param name="cutomerId"></param>
        /// <returns></returns>
        public bool GetCrmCustomerPublic(Guid CustomerId)
        {
            this.entity = new CrmCustomer();
            DbSql db = new DbSql();
            string strSql = "select count(*) from CrmCustomer where ID='" + CustomerId + "' and IsPrevate=0";
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 客户状态为私海
        /// </summary>
        /// <param name="cutomerId"></param>
        /// <returns></returns>
        public bool GetCrmCustomerPrivate(Guid CustomerId)
        {
            this.entity = new CrmCustomer();
            DbSql db = new DbSql();
            string strSql = "select count(*) from CrmCustomer where ID='" + CustomerId + "' and IsPrevate=1";
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 客户状态为下订单
        /// </summary>
        /// <param name="cutomerId"></param>
        /// <returns></returns>
        public bool GetCrmCustomerOrder(Guid CustomerId)
        {
            this.entity = new CrmCustomer();
            DbSql db = new DbSql();
            string strSql = "select count(*) from CrmCustomer where ID='" + CustomerId + "' and IsPrevate=2";
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 客户状态为退回
        /// </summary>
        /// <param name="cutomerId"></param>
        /// <returns></returns>
        public bool GetCrmCustomerBack(Guid CustomerId)
        {
            this.entity = new CrmCustomer();
            DbSql db = new DbSql();
            string strSql = "select count(*) from CrmCustomer where ID='" + CustomerId + "' and IsPrevate=3";
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 客户状态为报废后修改
        /// </summary>
        /// <param name="cutomerId"></param>
        /// <returns></returns>
        public bool GetCrmCustomerBaoFei(Guid CustomerId)
        {
            this.entity = new CrmCustomer();
            DbSql db = new DbSql();
            string strSql = "select count(*) from CrmCustomer where ID='" + CustomerId + "' and IsPrevate=5";
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取客户是否为意向
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public bool GetCrmCustomerIsWill(Guid CustomerId)
        {
            this.entity = new CrmWillCustomer();
            DbSql db = new DbSql();
            string strSql = "select count(*) from CrmWillCustomer where CustomerID='" + CustomerId + "'";
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 获取客户是否已退回
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public bool GetCrmCustomerIsReturn(Guid CustomerId)
        {
            this.entity = new CrmCustomerReturn();
            DbSql db = new DbSql();
            string strSql = "select count(*) from CrmCustomerReturn where CustomerId='" + CustomerId + "'";
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 获取客户是否为准备
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public bool GetCrmCustomerIsReady(Guid CustomerId)
        {
            this.entity = new CrmCustomerReady();
            DbSql db = new DbSql();
            string strSql = "select count(*) from CrmCustomerReady where CustomerID='" + CustomerId + "'";
            int iCount = db.ExecuteScalar(strSql);
            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 客户资料编辑时显示所有订单信息
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<V_CrmCustomerOrder> GetAllOrderInfo(Guid CustomerId)
        {
            this.entity = new V_CrmCustomerOrder();
            return GetEntitysBySqlComm<V_CrmCustomerOrder>("select * from V_CrmCustomerOrder where CustomerId='" + CustomerId + "' order by AddTime desc");
        }
        /// <summary>
        /// 客户资料编辑时显示所有订单信息
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public IList<V_CrmCustomerInfoOrder> GetAllOrderInfos(Guid CustomerId)
        {
            this.entity = new V_CrmCustomerInfoOrder();
            return GetEntitysBySqlComm<V_CrmCustomerInfoOrder>(@"select  DISTINCT 
                      ID, Code, CustomerId, DeptId, Date, TypeID, ValutaID, PillID, CourseLoyoutID, ResignMoney, TotalMoney, PaymentId, StateID, Explain, CheckID, 
                      CheckTime, AddUser, AddTime, EXP1, EXP2, EXP3, EXP4, PaymentType, OrderDeptName, OrderAddUName, OrderValutaName, OrderStateName, 
                      OrderTypeName, CustomerCode, State, Province, City, Area, Address, CustomerName, MnemonicCode, IsHQ, DecisionPosition, Decisionmaker, 
                      DecisionTel, DecisionEmail, Tel2, Post, Fax, Email, CompanyNetting, CompanyCreateDate, CompanyProduct, CompanyAttention, CompanyGain, 
                      PersonSum, PersonSum2, PersonSum3, ProductSum, ProductSum2, ProductSum3, CusTestimony, RADTargetId, CusTrainbudget, ManagerQuestion, 
                      StrongPoint, IsPrevate, AttachList, IsValue, CustomerExplain, SaleadviserId, SaleaddTime, CustomerAdduser, CustomerAddTime, TrainHistory, Painful, 
                      Pullulation, LectorUser, Lectortime, CustomeerMgr, Mgrtime, LevelName, OriginName, TypeName, TradeName, DeptName, ProductName, 
                      PersonName, SaleadviserName, AddUserName, LectorName, CustomerMgrName, DecisionPersonsun, HighPersonsum, MiddlePersonsum, 
                      BelowPersonsum, TrainCompany, TrainDate, AffectDecisioner, CustomerPhylogeny, Homeplace, BirthdayType, DecisionBirthday, SchoolScientifc, 
                      DecisionFancy, Credit, Character, Tel1, DecisionTick, DecisionSex, DecisionRest, DecisionLearn, DecisionFeelType, DecisionType, DecisionBuyType, 
                      DecisionAttitude, PayOffNum1, PayOffNum2, PayOffNum3, CustomerSaleTyte, IsTrainDept, CompeterCustomer, TradeDegree, CustomerDream, 
                      CustomerPromis, ProfitNum1, ProfitNum2, ProfitNum3, DecisionTypName, DecisionBuyTypeName, DecisionFeelTypeName, CustomerSaleTypeName, 
                      WorkFlowId, WorkFlowInsId, WorkTaskId, WorkTaskInsId, CheckUser, ProductPackID, IsHistory
                      from V_CrmCustomerInfoOrder where CustomerId='" + CustomerId + "' order by AddTime desc");
        }
        /// <summary>
        /// 根据客户ID显示所有缴款单信息         
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public IList<V_CrmPay> GetAllPayInfo(Guid CustomerId)
        {
            this.entity = new V_CrmPay();
            return GetEntitysBySqlComm<V_CrmPay>("select * from V_CrmPay where CustomerId='" + CustomerId + "' order by Addtime desc");
        }
        public IList<V_CrmProductPay> GetProductPayByOrderProductId(Guid OrderProductId)
        {
            this.entity = new V_CrmProductPay();
            return GetEntitysBySqlComm<V_CrmProductPay>("select * from V_CrmProductPay where OrderProductId='" + OrderProductId + "'");
        }
        /// <summary>
        /// 根据内训ID显示所有讲师信息        
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public IList<V_CrmDeliverTeacher> GetAllTeacherInfo(Guid MajorProjectID, string nxja)
        {
            this.entity = new V_CrmDeliverTeacher();
            if (nxja == "nxja")
            {
                return GetEntitysBySqlComm<V_CrmDeliverTeacher>("select top 1  dbo.fn_ConvertString(dbo.CrmDeliverTrain.PTeacher, ',') As Exp2,PTeacher As Exp1 from CrmDeliverTrain where ID='" + MajorProjectID + "' order by AddTime desc");
            }
            return GetEntitysBySqlComm<V_CrmDeliverTeacher>("select * from V_CrmDeliverTeacher where MajorProjectID='" + MajorProjectID + "' order by AddTime desc");
        }
        /// <summary>
        /// 根据客户ID显示所有讲师总结信息
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public IList<V_CrmTeachSumup> GetAllTeachSumup(Guid CustomerId)
        {
            this.entity = new V_CrmTeachSumup();
            return GetEntitysBySqlComm<V_CrmTeachSumup>("select * from V_CrmTeachSumup where CustomerId='" + CustomerId + "' order by Addtime desc");
        }
        public bool returnResult(Guid id, Guid stateid)
        {
            DbSql db = new DbSql();
            int iResult = 0;
            IDataParameter[] _Parameters1 = new IDataParameter[] { };
            SqlParameter IDs = new SqlParameter("@ID", SqlDbType.UniqueIdentifier, 50);
            IDs.Direction = ParameterDirection.Input;
            IDs.Value = id;
            SqlParameter States = new SqlParameter("@State", SqlDbType.UniqueIdentifier, 50);
            States.Direction = ParameterDirection.Input;
            States.Value = stateid;
            SqlParameter ReturnValue1 = new SqlParameter("@ReturnValue", SqlDbType.UniqueIdentifier, 50);
            ReturnValue1.Direction = ParameterDirection.Output;
            _Parameters1 = new IDataParameter[] { IDs, States, ReturnValue1 };
            if (db.RunProcedureReturnString("sp_UpdateCrmPay", _Parameters1, out iResult) == "1")
            { return true; }
            else { return false; }
        }
        /// <summary>
        /// 根据部门获取所有职员信息
        /// </summary>
        /// <param name="DeptName">部门名称</param>
        /// <returns></returns>
        public IList<V_DatEmployeeForUser> GetDatEmployeeByDeptId(string DeptId)
        {
            this.entity = new V_DatEmployeeForUser();
            IList<V_DatEmployeeForUser> list = null;
            if (DeptId == "11") //查找所有部门
            {
                list = GetEntitysBySqlComm<V_DatEmployeeForUser>("select * from V_TrSendQuestionnaireUser order by Name");
            }
            else
            {
                list = GetEntitysBySqlComm<V_DatEmployeeForUser>("select * from V_TrSendQuestionnaireUser where DeptID='" + CSVHelper.FmSQL(DeptId) + "' order by Name");
            }
            return list;
        }
        /// <summary>
        /// 根据名称查找所有职员信息
        /// </summary>
        /// <param name="strName">职员名称</param>
        /// <returns></returns>
        public IList<V_DatEmployeeForUser> GetDatEmployeeByName(string strName)
        {
            this.entity = new V_DatEmployeeForUser();
            IList<V_DatEmployeeForUser> list = GetEntitysBySqlComm<V_DatEmployeeForUser>("select * from V_TrSendQuestionnaireUser where Name like '%" + strName + "%' order by Name");
            return list;
        }
        /// <summary>
        /// 退回修改客户信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public CrmCustomerReturn GetCrmCustomerReturn(Guid Id)
        {
            this.entity = new CrmCustomerReturn();
            return this.GetEntityBySqlComm("select * from CrmCustomerReturn where ID='" + Id + "'") as CrmCustomerReturn;
        }
        /// <summary>
        /// 获取订单期数集合数据
        /// </summary>
        /// <param name="ClassId"></param>
        /// <returns></returns>
        public IList<CrmProductLayout> GetCrmProductLayout()
        {
            this.entity = new CrmProductLayout();
            return GetEntitysBySqlComm<CrmProductLayout>("select * from CrmProductLayout order by AddTime desc");
        }
        /// <summary>
        /// 获取客户分配记录集合
        /// </summary>
        /// <returns></returns>
        public IList<V_CrmCustomerRecord> GetCrmCustomerRecord(Guid CustomerId)
        {
            this.entity = new V_CrmCustomerRecord();
            return GetEntitysBySqlComm<V_CrmCustomerRecord>("select * from V_CrmCustomerRecord where CustomerID='" + CustomerId + "' order by AddTime desc");
        }
        /// <summary>
        /// 根据客户ID获取客户关系列表
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public IList<V_CrmCustomerRelationInfo> GetCrmCustomerRelationInfoByCustomerId(Guid CustomerId)
        {
            this.entity = new V_CrmCustomerRelationInfo();
            return GetEntitysBySqlComm<V_CrmCustomerRelationInfo>("select * from V_CrmCustomerRelationInfo where CustomerID='" + CustomerId + "' order by AddTime desc");
        }
        /// <summary>
        /// 获取客户关系列表
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public IList<V_CrmCustomerRelationInfo> GetCrmCustomerRelationInfo(Guid Id)
        {
            this.entity = new V_CrmCustomerRelationInfo();
            return GetEntitysBySqlComm<V_CrmCustomerRelationInfo>("select * from V_CrmCustomerRelationInfo where ID='" + Id + "' order by AddTime desc");
        }
        /// <summary>
        /// 获取客户数据
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public CrmCustomerOld GetCrmCustomerOldData(Guid Id)
        {
            this.entity = new CrmCustomerOld();
            return this.GetEntityBySqlComm("select * from CrmCustomerOld where ID='" + Id + "'") as CrmCustomerOld;
        }

        #region======================统计======================
        /// <summary>
        /// 获取客户信息(客户统计)
        /// </summary>
        /// <param name="CustomerId">客户ID</param>
        /// <returns></returns>
        public DataSet GetAllCustomerInfoRpt(int PageSize, int PageIndex, string strEntity, string strOrder, string strCondition)
        {
            DbSql db = new DbSql();

            string strSql = "SELECT * FROM  ( SELECT ROW_NUMBER() OVER( Order by " + strOrder + ")  AS RowNumber," +
                      "* FROM V_CrmCustomerInfo where 1=1 and " + strCondition + ") A WHERE RowNumber between " + ((PageIndex * PageSize) + 1) + " and " + ((PageIndex * PageSize) + 25);
            return db.Query(strSql);
        }
        #endregion
        /// <summary>
        /// 根据客户ID获取所有公开课信息
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public IList<V_CrmDeliverMajor> GetAllDeliverMajorByCustomerId(Guid CustomerId)
        {
            this.entity = new V_CrmDeliverMajor();
            return GetEntitysBySqlComm<V_CrmDeliverMajor>("select * from V_CrmDeliverMajor where CustomerId='" + CustomerId + "' order by AddTime desc");
        }
        public IList<V_CrmDeliverMajor> GetAllDeliverMajorByOrderProductId(Guid OrderProductId)
        {
            this.entity = new V_CrmDeliverMajor();
            return GetEntitysBySqlComm<V_CrmDeliverMajor>("select * from V_CrmDeliverMajor where OrderProductID='" + OrderProductId + "' order by AddTime desc");
        }
        /// <summary>
        /// 根据客户ID获取所有内训信息
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public IList<V_CrmDeliverTrain> GetAllDeliverTrainByCustomerId(Guid CustomerId)
        {
            this.entity = new V_CrmDeliverTrain();
            return GetEntitysBySqlComm<V_CrmDeliverTrain>("select * from V_CrmDeliverTrain where CustomerId='" + CustomerId + "' order by AddTime desc");
        }
        public IList<V_CrmDeliverTrain> GetAllDeliverTrainByOrderProductId(Guid OrderProductId)
        {
            this.entity = new V_CrmDeliverTrain();
            return GetEntitysBySqlComm<V_CrmDeliverTrain>("select * from V_CrmDeliverTrain where OrderProductID='" + OrderProductId + "' order by AddTime desc");
        }
        /// <summary>
        /// 根据客户ID获取所有项目信息
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public IList<V_CrmDeliverMajorProInfo> GetAllDeliverProInfoByCustomerId(Guid CustomerId)
        {
            this.entity = new V_CrmDeliverMajorProInfo();
            return GetEntitysBySqlComm<V_CrmDeliverMajorProInfo>("select * from V_CrmDeliverMajorProInfo where CustomerId='" + CustomerId + "' order by AddTime desc");
        }
        public IList<V_CrmDeliverMajorProInfo> GetAllDeliverProInfoByOrderProductId(Guid OrderProductId)
        {
            this.entity = new V_CrmDeliverMajorProInfo();
            return GetEntitysBySqlComm<V_CrmDeliverMajorProInfo>("select * from V_CrmDeliverMajorProInfo where OrderProductID='" + OrderProductId + "' order by AddTime desc");
        }
        /// <summary>
        /// 交付统计第一级别
        /// </summary>
        /// <returns></returns>
        public IList<V_CrmCustomerDeliverStatistics> GetAllDataDeliver()
        {
            this.entity = new V_CrmCustomerDeliverStatistics();
            return GetEntitysBySqlComm<V_CrmCustomerDeliverStatistics>("select * from V_CrmCustomerDeliverStatistics where Caption is not null and CourseLayoutName is null");
        }
        /// <summary>
        /// 交付统计第二级别
        /// </summary>
        /// <returns></returns>
        public IList<V_CrmCustomerDeliverStatistics> GetAllLayoutList(string productId)
        {
            this.entity = new V_CrmCustomerDeliverStatistics();
            return GetEntitysBySqlComm<V_CrmCustomerDeliverStatistics>("select * from V_CrmCustomerDeliverStatistics where Caption is not null and CourseLayoutName is not null and DeptName is null and Caption='" + productId + "'");
        }
        /// <summary>
        /// 交付统计第三级别
        /// </summary>
        /// <returns></returns>
        public IList<V_CrmCustomerDeliverStatistics> GetAllDeptList(string layoutId)
        {
            this.entity = new V_CrmCustomerDeliverStatistics();
            return GetEntitysBySqlComm<V_CrmCustomerDeliverStatistics>("select * from V_CrmCustomerDeliverStatistics where Caption is not null and CourseLayoutName is not null and DeptName is not null and CustomerName is null and CourseLayoutName='" + layoutId + "'");
        }
        /// <summary>
        /// 获取该客户是否存在未结案的缴款单
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public DataTable getIsExistPay(string customerId) {
            this.entity = new CrmPay();
            return this.GetDataBySqlComm("select ID from CrmPay where CustomerId='" + customerId + "'and [State]<>'86B67A2C-8DF8-417C-B70D-6D54969B812D' and WorkFlowId<>'fd37517b-ccf7-4fc5-9cee-18a04118bcfb'");
        }
        /// <summary>
        /// 获取该客户是否存在价值已经提交过了
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public DataTable getIsExistValuePay(string cId)
        {
            this.entity = new CrmValue_CrmProductPay();
            return this.GetDataBySqlComm("select ID from CrmValue_CrmProductPay where CrmProductPayID in ('" + cId + "')");
        }
        /// <summary>
        /// 获取该客户是否存在项目交付单已经提交过了
        /// </summary>
        /// <param name="cid">订单ID  ；typeid 项目类； stateid 待交付</param>
        /// <returns></returns>
        public DataTable getIsExistProject(string cId)
        {
            this.entity = new CrmDeliverMajorP_Order();
            return this.GetDataBySqlComm("select ID from CrmDeliverMajorP_Order where OrderProductID in (select ID from CrmOrderProduct where OrderID='" + cId + "' and productid in (select Productid from CrmProduct where TypeId='3AEDE3F6-1915-48D5-AFB2-C9380DB26813' and StateId='AF73673F-9FEC-4D14-ACFD-B0FCE08D6FFA'))");
        }

        /// <summary>
        /// 更新渠道客户状态为退回
        /// </summary>
        /// <param name="workFlowInsId"></param>
        /// <returns></returns>
        public bool UpdateCrmCustomerChannelState(Guid WorkFlowInsId)
        {
            this.entity = new CrmCustomerChannel();
            return GetDataBySqlComm("update CrmCustomerChannel set WorkState=2 where WorkFlowInsId='" + WorkFlowInsId + "'") != null;
        }
    }
}
